package com.icss.th.db.tools;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.icss.th.db.pojo.Column;

public class Table 
{
	/**
	 * 表名
	 */
	private String name;
	
	/**
	 * 列集合
	 */
	private List<Column> columns;

	public String getName() 
	{
		return name;
	}

	public void setName(String name) 
	{
		this.name = name;
	}

	public List<Column> getColumns() 
	{
		return columns;
	}

	public void setColumns(List<Column> columns) 
	{
		this.columns = columns;
	}

	public Table(String name, List<Column> columns)
	{
		this.name = name;
		this.columns = columns;
	}
	
	/**
	 * 通过数据连接和表名获得table实例
	 * @param tableName
	 * @param conn
	 * @return
	 */
	public static Table getInstance(String tableName, Connection conn)
	{
		if (tableName == null || "".equals(tableName) || conn == null) return null;
		
		Table table = null;
		
		try
		{
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from " + tableName + " where 1<>1");
			ResultSetMetaData metaData = rs.getMetaData();

			if (metaData != null)
			{
				List<Column> list = new ArrayList<Column>();
				for (int i = 1; i <= metaData.getColumnCount(); i++)
				{
					String name = metaData.getColumnName(i);
					int type = metaData.getColumnType(i);
					list.add(new Column(name, type));
				}
				
				table = new Table(tableName, list);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		return table;
	}
	
	/**
	 * 获得列用逗号连接的字符串
	 * @return
	 */
	private String getColumnsStr()
	{
		StringBuilder sb = new StringBuilder();
		
		if (columns != null && !columns.isEmpty())
		{
			for (Column c : columns)
			{
				sb.append(c.getName()).append(",");
			}
			
			sb.deleteCharAt(sb.length() - 1);
		}
		
		return sb.toString();
	}
	
	/**
	 * 获得查询SQL
	 * @return
	 */
	public String getQuerySql()
	{
		return "select " + getColumnsStr() + " from " + name;
	}
	
	/**
	 * 获得插入SQL
	 */
	public String getInsertSql()
	{
		StringBuilder sb = new StringBuilder();
		
		sb.append("insert into ")
		.append(name)
		.append("(").append(getColumnsStr()).append(") values (");
		
		int i = 0;
		while (i++ < columns.size())
		{
			sb.append("?,");
		}
		sb.deleteCharAt(sb.length() - 1).append(")");
		
		return sb.toString();
	}
}
